package DBTasks;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

public class DetailedAdoptionsForShelter extends DBTask {
    private static String createAdoptionsViewQuery = "CREATE OR REPLACE VIEW AdoptionSummary AS SELECT * FROM Adopts NATURAL JOIN Dog UNION ALL SELECT * FROM Adopts NATURAL JOIN Cat";
    private static String createFullAdoptionViewQuery = "CREATE OR REPLACE VIEW FullSummary AS SELECT * FROM AdoptionSummary JOIN Adopter ON Adopter.Name=AdoptionSummary.Adopter_Name WHERE DATE(Date)=? AND Shelter_Name=?";

    private static String getAdopterQuery = "SELECT Name, Date_Of_Birth, Phone, Address FROM FullSummary";
    private static String getPetIDQuery = "SELECT Pet_ID FROM FullSummary";
    private static String getIfDogQuery = "SELECT Pet_ID FROM Dog WHERE Pet_ID=?";

    public static ArrayList<Adoption> getDetailedAdoptions(String date, String shelterName) {
        Date providedDate = convertStringToDate(date);
        boolean validDate = validateDate(providedDate);
        ArrayList<Adoption> adoptions = new ArrayList<Adoption>();

        if (validDate) {
            java.sql.Date sqlDate = new java.sql.Date(providedDate.getTime());
            try {
                Statement executer = con.createStatement();
                PreparedStatement createAdoptionsView = con.prepareStatement(createFullAdoptionViewQuery);

                executer.executeUpdate(createAdoptionsViewQuery);
                createAdoptionsView.setDate(1, sqlDate);
                createAdoptionsView.setString(2, shelterName);
                createAdoptionsView.executeUpdate();

                ResultSet adopterInfo = executer.executeQuery(getAdopterQuery);
                while (adopterInfo.next()) {
                    String name = adopterInfo.getString("Name");
                    String phone = adopterInfo.getString("Phone");
                    String address = adopterInfo.getString("Address");
                    String dateOfBirth = adopterInfo.getString("Date_Of_Birth");

                    ResultSet petIds = createAdoptionsView.executeQuery(getPetIDQuery);
                    while (petIds.next()) {
                        int idToCheck = petIds.getInt("Pet_ID");
                        boolean isDog =  checkType(idToCheck);
                        Adoption adoption;
                        if (isDog) {
                            adoption = new Adoption(name, phone, address, dateOfBirth, idToCheck, "Dog");
                        }
                        else {
                            adoption = new Adoption(name, phone, address, dateOfBirth, idToCheck, "Cat");
                        }
                        adoptions.add(adoption);
                    }
                }

                executer.executeUpdate("DROP VIEW FullSummary,AdoptionSummary");
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return adoptions;
    }

    private static boolean checkType(int petId) {
        boolean isDog = false;
        try {
            PreparedStatement checkType = con.prepareStatement(getIfDogQuery);
            checkType.setInt(1, petId);

            checkType.execute();
            ResultSet dogPetIds = checkType.getResultSet();
            while (dogPetIds.next()) {
                if (dogPetIds.getInt("Pet_ID") == petId) {
                    isDog = true;
                    break;
                }
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return isDog;
    }

    private static boolean validateDate(Date date) {
        Timestamp currentDate = new Timestamp(new Date().getTime());
        if (date.after(currentDate)) {
            return false;
        }

        return true;
    }

    private static Date convertStringToDate(String date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date providedDate = null;
        try {
            providedDate = sdf.parse(date);
        }
        catch (ParseException e1) {
            e1.printStackTrace();
        }

        return providedDate;
    }
}
